/**
 * Company : SIANGKLOM.
 * Created By : Chatree Siangklom
 * Created On : Oct 9, 2013  3:03:19 PM 
 * e-Mail : dr_chatree@hotmail.com
 * Mobile Phone : (+66) 087-921-5115
 * Package Name : com.tis.database.dao.promis.impl
 * File Name : VwSyncGlPostingDetailDaoImpl.java
 * TODO
 */
package com.tis.database.dao.promis.impl;

import java.util.Collections;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import promis.utility.CommonService;

import com.tis.database.dao.ClsInstance;
import com.tis.database.dao.impl.AbstractDaoImpl;
import com.tis.database.dao.promis.VwSyncGlPostingDetailDao;
import com.tis.database.entitys.DocumentNotSend;
import com.tis.database.entitys.promis.VwSyncGlPostingDetail;

/**
 * @author Chatree Siangklom of Administrator
 * Date =Oct 9, 2013 : 3:03:19 PM 
 * CreateBy=Chatree Siangklom
 * 
 * VwSyncGlPostingDetailDaoImpl
 * TODO
 */
@Repository
@SuppressWarnings("unchecked")
public class VwSyncGlPostingDetailDaoImpl extends AbstractDaoImpl<VwSyncGlPostingDetail, String>  implements VwSyncGlPostingDetailDao {

	private static final Logger logging = LoggerFactory.getLogger(VwSyncGlPostingDetailDaoImpl.class);
	
	/**
	 * Date =Oct 9, 2013 : 3:04:41 PM 
	 * CreateBy=Chatree Siangklom
	 * VwSyncGlPostingDetailDaoImpl
	 * TODO
	 * @param entityClass
	 */
	protected VwSyncGlPostingDetailDaoImpl(){
			super(VwSyncGlPostingDetail.class);
	}
	
	public List<DocumentNotSend> SearchByIsNotSend(int maxResult)  throws Exception {
		try{
			Session session = getCurrentSession();
			StringBuilder sql = new StringBuilder();
			sql.append(" SELECT  GL_POSTING_DETAILS_ID, COMPANY,PROJECT,DOCUMENT_NO,CREATED_BY, CREATED_ON, SYNC_ISSYNC, SYNC_ISSTAGING "); 
			sql.append(" FROM "+ ClsInstance.PROMIS_SCHEMA + ClsInstance.POINTER +"VW_SYNC_GL_POSTING_DETAILS ");
			sql.append(" WHERE 1 = 1   ");
			sql.append(" AND ((NVL(SYNC_ISSTAGING,'N') = 'N') OR (NVL(SYNC_ISSYNC,'N') = 'N') ) "); 
			sql.append(" GROUP BY GL_POSTING_DETAILS_ID, COMPANY,PROJECT,DOCUMENT_NO,CREATED_BY, CREATED_ON, SYNC_ISSYNC, SYNC_ISSTAGING "); 
			sql.append(" ORDER BY GL_POSTING_DETAILS_ID DESC, COMPANY DESC,PROJECT DESC,DOCUMENT_NO DESC,CREATED_BY DESC, CREATED_ON DESC, SYNC_ISSYNC DESC, SYNC_ISSTAGING DESC ");
			
			Query q = session.createSQLQuery(sql.toString());
			
			if(maxResult > 0)
				q.setMaxResults(maxResult);	
			
			List lst = q.list();
			logging.info("Size=" + CommonService.getSize(lst));
			
			return Collections.checkedList(lst, DocumentNotSend.class);
		}catch(Exception e){
			e.printStackTrace();
			logging.error(e.getMessage());
			throw e;
		}
	}

	/** (Javadoc)
	 * @see com.tis.database.dao.promis.VwSyncGlPostingDetailDao#Search(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, int)
	 * Date =Oct 11, 2013 : 4:14:05 PM 
	 * CreateBy=Chatree Siangklom
	 * TODO
	 */
	public List<VwSyncGlPostingDetail> Search(	String company, 
												String project, 
												String documentNo, 
												String refDocNo, 
												String narration,
												String accountCode, 
												String postingKey, 
												String status,
												String syncIsstaging, 
												String syncIssync, 
												String postingDateStart,
												String postingDateEnd, 
												int maxResult) throws HibernateException,
			Exception {
		try {
			
			Session session = getCurrentSession();
			Query q = session.getNamedQuery(VwSyncGlPostingDetail.findBySearch);
			String query = q.getQueryString();
			
			if(!CommonService.IsNull(company))
				query += " And upper(c.company) like '%"+ CommonService.IsNullRString(company).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(project))
				query += " And upper(c.project) like '%"+ CommonService.IsNullRString(project).toUpperCase() +"%' ";
			
			if(!CommonService.IsNull(documentNo))
				query += " And upper(c.documentNo) like '%"+ CommonService.IsNullRString(documentNo).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(refDocNo))
				query += " And upper(c.refDocNo) like '%"+ CommonService.IsNullRString(refDocNo).toUpperCase() +"%'";
						
			if(!CommonService.IsNull(narration))
				query += " And upper(c.narration) like '%"+ CommonService.IsNullRString(narration).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(accountCode))
				query += " And upper(c.accountCode) like '%"+ CommonService.IsNullRString(accountCode).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(postingKey))
				query += " And upper(c.postingKey) like '%"+ CommonService.IsNullRString(postingKey).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(status))
				query += " And upper(c.status) like '%"+ CommonService.IsNullRString(status).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(syncIsstaging))
				query += " And upper(c.syncIsstaging) like '%"+ CommonService.IsNullRString(syncIsstaging).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(syncIssync))
				query += " And upper(c.syncIssync) like '%"+ CommonService.IsNullRString(syncIssync).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(postingDateStart) && !CommonService.IsNull(postingDateEnd))
				query += " And to_char(c.postingDate, 'YYYY-MM-DD') BETWEEN :postingDateStart And :postingDateEnd ";
			
			query += " Order by :p_orderby "; 	
			
			logging.info(query);
			
			q = session.createQuery(query);
			
			if(!CommonService.IsNull(postingDateStart))
				q.setParameter("postingDateStart", CommonService.IsNullRString(postingDateStart).toUpperCase());
			
			if(!CommonService.IsNull(postingDateEnd))
				q.setParameter("postingDateEnd", CommonService.IsNullRString(postingDateEnd).toUpperCase());
			
			q.setParameter("p_orderby", "asc");
			
			if(maxResult > 0)
				q.setMaxResults(maxResult);			
			
			List<VwSyncGlPostingDetail> lst = Collections.checkedList(q.list(), VwSyncGlPostingDetail.class);
			if(CommonService.getSize(lst) > 0 )
				return lst;
			else 
				return null;
			
		}catch(HibernateException ex) {
			logging.error(ex.getMessage());
			ex.printStackTrace();
			throw ex;
		}
		catch(Exception ex1) {
			logging.error(ex1.getMessage());
			ex1.printStackTrace();
			throw ex1;
		}
	}

	/** (Javadoc)
	 * @see com.tis.database.dao.promis.VwSyncGlPostingDetailDao#SearchByDocumentNo(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 * Date =Oct 11, 2013 : 4:14:05 PM 
	 * CreateBy=Chatree Siangklom
	 * TODO
	 */
	public List<VwSyncGlPostingDetail> SearchByDocumentNo(	String company, 
															String project, 
															String documentNo, 
															String status, 
															String refDocNo) throws HibernateException, Exception {
		try {
			
			Session session = getCurrentSession();
			Query q = session.getNamedQuery(VwSyncGlPostingDetail.findByDocumentNo);
			String query = q.getQueryString();
			
			if(!CommonService.IsNull(company))
				query += " And upper(c.company) = '"+ CommonService.IsNullRString(company).toUpperCase() +"' ";
			
			if(!CommonService.IsNull(project))
				query += " And upper(c.project) = '"+ CommonService.IsNullRString(project).toUpperCase() +"' ";
			
			if(!CommonService.IsNull(documentNo))
				query += " And upper(c.documentNo) = '"+ CommonService.IsNullRString(documentNo).toUpperCase() +"' ";
			
			if(!CommonService.IsNull(refDocNo))
				query += " And upper(c.refDocNo) = '"+ CommonService.IsNullRString(refDocNo).toUpperCase() +"' ";
			
			if(!CommonService.IsNull(status))
				query += " And upper(c.status) = '"+ CommonService.IsNullRString(status).toUpperCase() +"' ";
			
			query += " Order by c.glPostingSeqNumber asc ";
			logging.info(query);
			
			q = session.createQuery(query);
			List<VwSyncGlPostingDetail> lst = Collections.checkedList(q.list(), VwSyncGlPostingDetail.class);
			if(CommonService.getSize(lst) > 0 )
				return lst;
			else 
				return null;
			
		}catch(HibernateException ex) {
			logging.error(ex.getMessage());
			ex.printStackTrace();
			throw ex;
		}
		catch(Exception ex1) {
			logging.error(ex1.getMessage());
			ex1.printStackTrace();
			throw ex1;
		}
	}

	/** (Javadoc)
	 * @see com.tis.database.dao.promis.VwSyncGlPostingDetailDao#SearchByVoucherType(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 * Date =Oct 11, 2013 : 4:22:29 PM 
	 * CreateBy=Chatree Siangklom
	 * TODO
	 */
	public List<VwSyncGlPostingDetail> SearchByVoucherType(	String company,
															String project, 
															String documentNo, 
															String voucherType,
															String syncIsstaging, 
															String syncIssync, 
															String status,
															String refDocNo,
															int maxResult) throws HibernateException, Exception {
		// TODO Auto-generated method stub
		try {
			
			Session session = getCurrentSession();
			Query q = session.getNamedQuery(VwSyncGlPostingDetail.findBackLogs);
			String query = q.getQueryString();
			
			if(!CommonService.IsNull(company))
				query += " And upper(c.company) like '%"+ CommonService.IsNullRString(company).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(project))
				query += " And upper(c.project) like '%"+ CommonService.IsNullRString(project).toUpperCase() +"%' ";
			
			if(!CommonService.IsNull(documentNo))
				query += " And upper(c.documentNo) like '%"+ CommonService.IsNullRString(documentNo).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(status))
				query += " And upper(c.status) like '%"+ CommonService.IsNullRString(status).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(voucherType))
				query += " And upper(c.voucherType) IN ("+ CommonService.IsNullRString(voucherType).toUpperCase() +") ";
			
			if(!CommonService.IsNull(syncIsstaging))
				query += " And upper(c.syncIsstaging) like '%"+ CommonService.IsNullRString(syncIsstaging).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(syncIssync))
				query += " And upper(c.syncIssync) like '%"+ CommonService.IsNullRString(syncIssync).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(refDocNo))
				query += " And upper(c.refDocNo) like '%"+ CommonService.IsNullRString(refDocNo).toUpperCase() +"%'";	
			
			query += " Order by :p_orderby "; 	
			
			logging.info(query);
			
			q = session.createQuery(query);
			
			q.setParameter("p_orderby", "ACS");
			
			if(maxResult > 0)
				q.setMaxResults(maxResult);	
			
			List<VwSyncGlPostingDetail> lst = Collections.checkedList(q.list(), VwSyncGlPostingDetail.class);
			if(CommonService.getSize(lst) > 0 )
				return lst;
			else 
				return null;
			
		}catch(HibernateException ex) {
			logging.error(ex.getMessage());
			ex.printStackTrace();
			throw ex;
		}
		catch(Exception ex1) {
			logging.error(ex1.getMessage());
			ex1.printStackTrace();
			throw ex1;
		}
	}
	
	

}
